﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;


public partial class UC_ucBCL_TH : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoLuong";
    static System.Data.DataTable dt;
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static double luongcb = 0, pcap = 0, luongtn = 0, tongluongcb = 0, tienthuong = 0, tiencongdem = 0, tienanca = 0, tienphat = 0, tienung = 0, tiencongdoan = 0, luongsl = 0, thuclinh = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("--Chọn tháng--", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            btnNext.Visible = false;
            btnPre.Visible = false;
            btnFirst.Visible = false;
            btnSave.Visible = false;
            LoadNhanVien(dlNhanVien);
            LoadBoPhan(dlBoPhan);
            LoadChucVu(dlChucVu);
        }
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();
        string ngaythang = "";
        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng lương tổng hợp";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "";
           
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BẢNG LƯƠNG TỔNG HỢP " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 16);
            sheet.AddMergedRegion(cra);
            rowIndex++;
            
            //if (dlLoaiCaBom.SelectedIndex > 0)
            //{
            //    //loai ca bom
                
            //    row = sheet.CreateRow(rowIndex);
            //    r1c1 = row.CreateCell(0);
            //    r1c1.SetCellValue(dlLoaiCaBom.SelectedIndex > 0 ? "Loại ca bơm: " + dlLoaiCaBom.SelectedItem.Text : "");
            //    r1c1.CellStyle = hs3;
                
            //}
            r1c1.Row.Height = 500;
            cra = new CellRangeAddress(3, 3, 0, 16);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Nhân viên","Bộ phận","Chức vụ","Lương CB","Phụ cấp","Lương trách nhiệm","Số ngày LV","Tổng lương CB","Tiền thưởng",
                              "Tiền công đêm","Tiền ăn ca","Tiền phạt","Tiền ứng","Tiền công đoàn","Lương SL","Thực lĩnh"};
            
            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j < dt.Columns.Count - 1; j++)
                {
                    cell = row.CreateCell(j);

                    if (j >= 4)
                    {
                        if (!dt.Rows[i][j-1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j-1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;

                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;


                }
               
            }

            #region Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(1);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "E", "F", "G", "H", "I", "J","K","L","M","N","O","P","Q" };
            for (int k = 4; k <= 16; k++)
            {
                cell = row.CreateCell(k);
                cell.CellFormula = "SUM(" + footer[k - 4] + "7:" + footer[k - 4] + rowIndex.ToString() + ")";
                cell.Row.Height = 500;
                cell.CellStyle = hs4;
            }



            for (int ik = 0; ik <= 18; ik++)
            {
                //fix lai border
                if (ik <= 3 && ik!=1)
                {
                    r1c1 = row.CreateCell(ik);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
                sheet.AutoSizeColumn(ik);
            }

            #endregion

            //end sheet tong hop--------------------------

            string ncc = "";
            if (dlTimKiem.SelectedIndex.Equals(0))
            {

                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        ncc = dt.Rows[i]["BoPhan"].ToString();

                        if (!ncc.Equals(dt.Rows[i - 1]["Bophan"].ToString()))
                        {
                            STT = 0;
                            #region Footer
                            //-----------Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(1);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 4; k <= 16; k++)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "SUM(" + footer[k - 4] + "7:" + footer[k - 4] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }



                            for (int ik = 0; ik <= 18; ik++)
                            {
                                //fix lai border
                                if (ik <= 3 && ik != 1)
                                {
                                    r1c1 = row.CreateCell(ik);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                                sheet.AutoSizeColumn(ik);
                            }
                            //---End Footer
                            #endregion
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "";

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BẢNG LƯƠNG TỔNG HỢP " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 16);
                            sheet.AddMergedRegion(cra);

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Bộ phận: " + ncc);
                            r1c1.CellStyle = hs2;
                            
                            r1c1.Row.Height = 500;
                            cra = new CellRangeAddress(3, 3, 0, 16);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            //End header-----------------------------------------------------------------------

                            STT++;

                        }
                    }
                    else
                    {
                        ncc = "";
                        if (!ncc.Equals(dt.Rows[i]["BoPhan"].ToString()))
                        {
                            ncc = dt.Rows[i]["BoPhan"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "";

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BẢNG LƯƠNG TỔNG HỢP " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 16);
                            sheet.AddMergedRegion(cra);
                            rowIndex++;

                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Bộ phận: "+ncc);
                            r1c1.CellStyle = hs2;

                            r1c1.Row.Height = 500;
                            cra = new CellRangeAddress(3, 3, 0, 16);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j < dt.Columns.Count - 1; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j >= 4)
                        {
                            if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                            }
                            else
                                cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                            cell.CellStyle = hsRight;

                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                            cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;


                    }

                    #region Footer
                    if (i == dt.Rows.Count - 1)
                    {
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(1);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 4; k <= 16; k++)
                        {
                            cell = row.CreateCell(k);
                            cell.CellFormula = "SUM(" + footer[k - 4] + "7:" + footer[k - 4] + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;
                        }



                        for (int ik = 0; ik <= 18; ik++)
                        {
                            //fix lai border
                            if (ik <= 3 && ik != 1)
                            {
                                r1c1 = row.CreateCell(ik);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                }
            }

        }
        using (var exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            //string filename = dlLoaiCaBom.SelectedIndex > 0 ? dlLoaiCaBom.SelectedItem.Text : "";
            string saveAsFileName = "BCL-TongHop" + " " + ngaythang + ".xls";

            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
            Response.Clear();
            Response.BinaryWrite(exportData.GetBuffer());
            Response.End();
        }
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = "";
            hdDenNgay.Value = "";
        }
        else
        {
            hdTuNgay.Value = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            hdDenNgay.Value = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }

    void LoadNhanVien(DropDownList dlLaiXe)
    {
        dlLaiXe.Items.Clear();
        dlLaiXe.DataSource = ex.GetData("sp_BCL_LoadNhanVien");
        dlLaiXe.DataBind();
        dlLaiXe.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dlLaiXe.SelectedIndex = 0;
    }
    void LoadBoPhan(DropDownList dlBoPhan)
    {
        dlBoPhan.Items.Clear();
        dlBoPhan.DataSource = ex.GetData("sp_xdabophan_ListDop");
        dlBoPhan.DataBind();
        dlBoPhan.Items.Insert(0, new ListItem("--Chọn bộ phận--", ""));
        dlBoPhan.SelectedIndex = 0;
    }
    void LoadChucVu(DropDownList dlChucVu)
    {
        dlChucVu.Items.Clear();
        dlChucVu.DataSource = ex.GetData("sp_xdachucvu_ListDrop");
        dlChucVu.DataBind();
        dlChucVu.Items.Insert(0, new ListItem("--Chọn chức vụ--", ""));
        dlChucVu.SelectedIndex = 0;
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        Search(1);

    }
    void Search(int page)
    {
        if (dlThang.SelectedIndex > 0)
        {
            int index = 1;
            if (dlNhanVien.SelectedIndex.Equals(0) && dlBoPhan.SelectedIndex.Equals(0) && dlChucVu.SelectedIndex.Equals(0))
                index = 1;
            else if (dlNhanVien.SelectedIndex > 0 && dlBoPhan.SelectedIndex.Equals(0) && dlChucVu.SelectedIndex.Equals(0))
                index = 2;
            else if (dlNhanVien.SelectedIndex.Equals(0) && dlBoPhan.SelectedIndex > 0 && dlChucVu.SelectedIndex.Equals(0))
                index = 3;
            else if (dlNhanVien.SelectedIndex.Equals(0) && dlBoPhan.SelectedIndex.Equals(0) && dlChucVu.SelectedIndex > 0)
                index = 4;

            p = new SqlParameter[6];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@NhanVien", ex.GetGuid(dlNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Index", index);
            p[3].SqlDbType = SqlDbType.Int;
            p[4] = new SqlParameter("@BoPhan", ex.GetGuid(dlBoPhan.SelectedValue));
            p[4].SqlDbType = SqlDbType.UniqueIdentifier;
            p[5] = new SqlParameter("@ChucVu", ex.GetGuid(dlChucVu.SelectedValue));
            p[5].SqlDbType = SqlDbType.UniqueIdentifier;
            //p[4] = new SqlParameter("@PageNumber", page);
            //p[4].SqlDbType = SqlDbType.Int;
            //p[5] = new SqlParameter("@RowspPage", PageNumber);
            //p[5].SqlDbType = SqlDbType.Int;
            dt = ex.GetData("sp_BCL_TongHop", p);
            GV.DataSource = dt;
            GV.DataBind();
            luongcb = pcap = luongtn = tongluongcb = tienthuong = tiencongdem = tienanca = tienphat = tienung = tiencongdoan = luongsl = thuclinh = 0;
            string dathanhtoan = "";
            for (int i = 0; i < GV.Rows.Count; i++)
            {
                luongcb += double.Parse(GV.Rows[i].Cells[4].Text.Trim().Replace(",", ""));
                pcap += double.Parse(GV.Rows[i].Cells[5].Text.Trim().Replace(",", ""));
                luongtn += double.Parse(GV.Rows[i].Cells[6].Text.Trim().Replace(",", ""));
                tongluongcb += double.Parse(GV.Rows[i].Cells[8].Text.Trim().Replace(",", ""));
                tienthuong += double.Parse(GV.Rows[i].Cells[9].Text.Trim().Replace(",", ""));
                tiencongdem += double.Parse(GV.Rows[i].Cells[10].Text.Trim().Replace(",", ""));
                tienanca += double.Parse(GV.Rows[i].Cells[11].Text.Trim().Replace(",", ""));
                tienphat += double.Parse(GV.Rows[i].Cells[12].Text.Trim().Replace(",", ""));
                tienung += double.Parse(GV.Rows[i].Cells[13].Text.Trim().Replace(",", ""));
                tiencongdoan += double.Parse(GV.Rows[i].Cells[14].Text.Trim().Replace(",", ""));
                luongsl += double.Parse(GV.Rows[i].Cells[15].Text.Trim().Replace(",", ""));
                thuclinh += double.Parse(GV.Rows[i].Cells[16].Text.Trim().Replace(",", ""));

                LinkButton lbt = (LinkButton)GV.Rows[i].Cells[0].FindControl("lbtDel");
                dathanhtoan = lbt.Text.Trim();
                CheckBox ckChi = (CheckBox)GV.Rows[i].Cells[0].FindControl("ckChi");

                if (dathanhtoan.Trim().Equals("X"))
                    ckChi.Enabled = false;
                else ckChi.Enabled = true;
            }
            if (GV.Rows.Count > 0)
            {
                GV.FooterRow.Cells[1].Text = "Tổng cộng";
                GV.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;

                GV.FooterRow.Cells[4].Text = ex.ConvertDecimal(luongcb);
                GV.FooterRow.Cells[5].Text = ex.ConvertDecimal(pcap);
                GV.FooterRow.Cells[6].Text = ex.ConvertDecimal(luongtn);
                GV.FooterRow.Cells[8].Text = ex.ConvertDecimal(tongluongcb);
                GV.FooterRow.Cells[9].Text = ex.ConvertDecimal(tienthuong);
                GV.FooterRow.Cells[10].Text = ex.ConvertDecimal(tiencongdem);
                GV.FooterRow.Cells[11].Text = ex.ConvertDecimal(tienanca);
                GV.FooterRow.Cells[12].Text = ex.ConvertDecimal(tienphat);
                GV.FooterRow.Cells[13].Text = ex.ConvertDecimal(tienung);
                GV.FooterRow.Cells[14].Text = ex.ConvertDecimal(tiencongdoan);
                GV.FooterRow.Cells[15].Text = ex.ConvertDecimal(luongsl);
                GV.FooterRow.Cells[16].Text = ex.ConvertDecimal(thuclinh);
                btnSave.Visible = true;
            }
            else btnSave.Visible = false;
            
        }
        else gstGetMess("Chọn tháng", "");
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        PrintNPOI(dt);

    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        }
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    protected void btnThanhToan_Click(object sender, EventArgs e)
    {
        string sotien = "", sql = "";
        int count = 0;
        for (int j = 0; j < GV.Rows.Count; j++)
        {
            Label lbID = (Label)GV.Rows[j].Cells[19].FindControl("lbID");
            CheckBox ckChi = (CheckBox)GV.Rows[j].Cells[1].FindControl("ckChi");
            sotien = GV.Rows[j].Cells[18].Text.Trim().Replace(",", "");
            if (ckChi.Checked)
            {
                sql += "insert into xdathanhtoanluong values('" + Guid.NewGuid().ToString() + "'," + dlThang.SelectedValue + "," + dlNam.SelectedValue + ",'" + lbID.Text.Trim() + "'," + sotien + ") ";
                count++;
            }
        }
        if (sql.Trim() != "")
        {
            ex.ExecuteNonQuerry_Text(sql);
            Search(1);
            gstGetMess("Đã thanh toán cho " + count.ToString() + " nhân viên", "");
        }
        else
            gstGetMess("Chưa chọn nhân viên nào để thanh toán", "");
    }
    protected void ckAll_CheckedChanged(object sender, EventArgs e)
    {
        for (int i = 0; i < GV.Rows.Count; i++)
        {
            CheckBox ck1 = (CheckBox)GV.Rows[i].Cells[1].FindControl("ckChi");
            if (ck1.Enabled)
            {
                if (ckAll.Checked)
                    ck1.Checked = true;
                else
                    ck1.Checked = false;
            }
        }
    }
    protected void dlTimKiem_SelectedIndexChanged(object sender, EventArgs e)
    {
        dlNhanVien.SelectedIndex = 0;
        dlBoPhan.SelectedIndex = 0;
        dlChucVu.SelectedIndex = 0;

        if (dlTimKiem.SelectedIndex.Equals(0))
        {
            dlNhanVien.Visible = false;
            dlBoPhan.Visible = false;
            dlChucVu.Visible = false;

        }
        else if (dlTimKiem.SelectedIndex.Equals(1))
        {
            dlNhanVien.Visible = true;
            dlBoPhan.Visible = false;
            dlChucVu.Visible = false;
        }
        else if (dlTimKiem.SelectedIndex.Equals(2))
        {
            dlNhanVien.Visible = false;
            dlBoPhan.Visible = true;
            dlChucVu.Visible = false;
        }
        else if (dlTimKiem.SelectedIndex.Equals(3))
        {
            dlNhanVien.Visible = false;
            dlBoPhan.Visible = false;
            dlChucVu.Visible = true;
        }
    }
    protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        string ID = e.CommandArgument.ToString();
        Guid gID = new Guid(ID);
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@IDs", gID);
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        if (e.CommandName.Equals("Del"))
        {
            try
            {
                ex.ExecuteNonQuerry("sp_xdathanhtoanluong_Del", p);
                Search(1);
                gstGetMess("Xóa thành công", "");
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");
            }
        }
    }
}